import datetime
import json
import logging
import jsonpath
from common.utils.db_util import DBUtil
import re
from decimal import Decimal
from common.enums.common_enums import DBType
from common.utils.read_file import get_table_and_rows, read_excel_with_sheetname


logger = logging.getLogger(__name__)

def compare_db_excel(dict1, dict2, include_column=None, exclude_column=None):
    """
    比较两个字典值
    :param dict1: 数据库查询结果
    :param dict2: excel期望结果
    :param include_column: 只比较传入的key
    :param exclude_column: 不比较传入的key
    :return:两个字典不相同的部分
    """
    result = []
    # 类型转换
    dict1 = dict(map(lambda item: (item[0], type_transform(item[1])), dict1.items()))
    dict2 = dict(map(lambda item: (item[0], type_transform(item[1])), dict2.items()))
    # 只获取要求比较的列
    if include_column:
        dict1 = dict(filter(lambda item: (item[0] in include_column), dict1.items()))
        dict2 = dict(filter(lambda item: (item[0] in include_column), dict2.items()))
    # 排除不需比较的列
    if exclude_column:
        dict1 = dict(filter(lambda item: (item[0] not in exclude_column), dict1.items()))
        dict2 = dict(filter(lambda item: (item[0] not in exclude_column), dict2.items()))
    for k, v in dict1.items():
        if dict2[k] != v:
            result.append({k: (dict1[k], dict2[k])})
    return result


def compare_db_excel_multi(list1, list2, *, include_column=None, exclude_column=None, sorted_by=None):
    """
    比较两个list是否相同,其中,list存的是dict
    :param list1:
    :param list2:
    :param include_column:只比较传入的列
    :param exclude_column:不比较传入的列
    :param sorted_by:list排序后比较
    :return:返回两个list不同的部分
    """
    assert len(list1) == len(list2)
    result = []
    # 若未传入排序字段，则按照第一个list下dict的第一个键值排序
    sorted_by = sorted_by if sorted_by else list(list1[0].items())[0][0]
    list1 = sorted(list1, key=lambda item: item[sorted_by])
    list2 = sorted(list2, key=lambda item: item[sorted_by])
    for k, v in enumerate(list1):
        compare_result = compare_db_excel(v, list2[k], include_column, exclude_column)
        if compare_result:
            result.append(compare_result)
    return result


def query_and_compare(expect, dbinfo, sheet_name=None, excel_condition=None, sql_condition=None,
                      include_column=None,
                      exclude_column=None):
    """
    :param sql_condition:
    :param excel_condition:
    :param expect: 期望结果，包含excel中所有sheet
    :param dbinfo:数据库信息
    :param sheet_name: 要获取的sheet名，不传则为第一个sheet
    :param include_column:仅比较传入的列
    :param exclude_column:不比较传入的列
    :return:返回表格和数据库不同的部分
    """
    excel_db_result = query_expect_and_db(expect, dbinfo, sheet_name=sheet_name, excel_condition=excel_condition,
                                          sql_condition=sql_condition, include_column=include_column,
                                          exclude_column=exclude_column)
    compare_result = compare_db_excel_multi(excel_db_result[0], excel_db_result[1], include_column=include_column,
                                            exclude_column=exclude_column)
    return compare_result


def query_expect_and_db(expect, dbinfo, *, sheet_name=None, excel_condition=None, sql_condition=None,
                        include_column=None, exclude_column=None):
    """
        :param expect: 期望结果，包含excel中所有sheet
        :param dbinfo:数据库信息
        :param sheet_name: 要获取的sheet名，不传则为第一个sheet
        :param excel_condition: 查询excel的条件
        :param sql_condition: 查询数据库的条件
        :param include_column:仅获取传入的列
        :param exclude_column:不获取传入的列
        :return:以tuple返回excel期望结果、数据库查询结果
        """
    # 获取excel中的期望结果
    expect_table, expect_rows = get_table_and_rows(expect, sheet_name=sheet_name, condition=excel_condition,
                                                   include_column=include_column, exclude_column=exclude_column)
    # 获取数据库对应查询结果
    sql = generate_sql_from_excel((expect_table, expect_rows[0]), sql_condition=sql_condition)
    dbinfo.update({'sql': sql})
    db_result = query_db_as_dict(**dbinfo)
    return expect_rows, db_result


def type_transform(value):
    if isinstance(value, (datetime.datetime, datetime.date)):
        # 日期比较精确到秒，不常用
        # value = datetime.datetime.strftime(value, '%Y-%m-%d %H:%M:%S')
        # 日期比较精确到天
        value = datetime.datetime.strftime(value, '%Y-%m-%d')
    elif isinstance(value, Decimal):
        value = float(value)
    else:
        pass
    return value


def query_db_as_dict(**query_condition):
    """
    查询数据库并返回列名:列值的字典
    :param query_condition: {'dbtype':'数据库类型，如mysql、sqlserver、oracle等','dbname':'要执行sql的数据库名','sql':'要执行的sql语句'}
    :return: 数据库查询结果
    """
    # 从sql语句中分割出查询列
    columns = str(query_condition['sql']).split('from')[0]
    columns = [s for s in re.split(r'[,\s]+', columns) if s != ''][1:]
    conn = DBUtil(query_condition['dbtype'], query_condition['dbname'])
    rows = conn.execute_query(query_condition['sql'])
    data = [dict(zip(columns, row)) for row in rows]
    return data


def generate_sql_from_excel(data, *, sql_type='select', sql_condition=None, sheet_name=None, exclude_column=[]):
    """

    :param data:表名+数据行的tuple
    :param sql_type:要生成的sql语句类型不正确，必须为select、insert、delete其中之一
    :param sql_condition:
    :param sheet_name:
    :param exclude_column:排除部分列
    :return:返回sql语句
    """
    if sql_type not in ['select', 'insert', 'delete']:
        raise ValueError('要生成的sql语句类型不正确，必须为select、insert、delete其中之一！')
    if isinstance(sql_condition, dict):
        where_condition = ' and ' + ' and '.join(
            [str(condition[0] + '=' + "'" + str(condition[1])) + "'" for condition in sql_condition.items()])
    elif isinstance(sql_condition, str):
        where_condition = ' and ' + sql_condition

    if sql_type == 'select':
        table_name = data[0]
        row = data[1]
        columns = ','.join([column for column in row.keys() if column not in exclude_column])
        sql = f'select {columns} from {table_name} where 1=1' + where_condition

    elif sql_type == 'insert':
        sql = []
        table_and_rows = get_table_and_rows(data, sheet_name=sheet_name)
        table_name = table_and_rows[0]
        rows = table_and_rows[1]
        for row in rows:
            columns = ','.join([column for column in row.keys() if row[column]])
            values = ','.join(["'" + str(row[column]) + "'" for column in row.keys() if row[column]])
            one_sql = f'insert into {table_name}({columns}) values ({values})'
            sql.append(one_sql)

    elif sql_type == 'delete':
        if not sql_condition:
            raise ValueError('delete语句必须带条件!')
        sql = []
        table_and_rows = get_table_and_rows(data)
        table_name = table_and_rows[0]
        rows = table_and_rows[1]
        for row in rows:
            one_sql = f'delete from {table_name} where 1=1' + sql_condition
            sql.append(one_sql)
    return sql


def generate_sql_and_dbinfo(data, dbinfo_table, sql_type='select'):
    """
    生成数据库信息+sql语句
    :param data: excel数据，格式为{sheet名: sheet数据行}
    :param dbinfo_table:
    :param sql_type: 要生成的sql语句类型，包含select、insert、delete
    :return:字典列表，格式为[{'dbname': '数据库名','dbtype': '数据库类型','sql': [sql语句列表]}]
    """
    result = []
    for sheet in data:
        # 获取小写的sheet名
        sheet_name = list(sheet.items())[0][0]
        sql = generate_sql_from_excel(data, sql_type=sql_type, sheet_name=sheet_name)
        for relation in dbinfo_table:
            # 不区分大小写,根据sheet名获取数据库映射关系
            if str(sheet_name).lower() in [str(tablename).lower() for tablename in relation[0]]:
                sql_item = {**relation[1], **{'sql': sql}}
                result.append(sql_item)
                break
    return result


def assert_response_simple(expect, response):
    """ 响应断言 """
    for k, v in json.loads(expect).items():
        assert response[k] == v


def assert_response(expect, response):
    """ 响应断言 """
    # 测试数据
    # expect = '($.success,=,true),\n($.result.name,包含,测试接口),\n($.result.labels,为空),'
    # response = {'success': True, 'result': {'id': 249, 'name': '测试接口(2024-09-08 18: 00: 02.746)', 'labels': '', 'projectId': 4, 'apiSuiteId': 35, 'method': 'GET', 'envId': -1, 'domain': 'http: //www.1111.com', 'path': '/s?kw=selenium', 'type': 1, 'remark': '用于自动化测试的接口', 'status': 0, 'beforeExcs': [], 'reqHeader': [], 'reqQuery': [{'key': 'kw', 'value': 'selenium'}], 'reqBodyData': [], 'reqBodyJson': None, 'reqBodyType': None, 'reqExtract': [], 'reqAssert': [], 'debugRsp': None, 'createBy': 'admin', 'createTime': None, 'updateBy': 'admin', 'updateTime': None}, 'error': None}
    expect_list = re.split(r'\)\s*[,，]*\s*\n*\s*', expect)
    expect_list = [i[1:] for i in expect_list if i != '']
    expect_format = []
    for expression in expect_list:
        items = re.split(r',|，', expression)
        if len(items) == 3:
            expect_format.append({'var': items[0], 'operator': items[1], 'result': items[2]})
        if len(items) == 2:
            expect_format.append({'var': items[0], 'operator': items[1]})
    assert_result = []
    # 对每一条断言分别判断
    for expression in expect_format:
        var = expression['var']
        expect_value = expression.get('result')
        response_value = jsonpath.jsonpath(response, expression['var'])[0]
        if expression['operator'] == '=':
            if isinstance(response_value, bool):
                expect_value_transfrom = True if expect_value in ['true', 'True', 'TRUE', '1'] else False
                if response_value != expect_value_transfrom:
                    assert_result.append(f'字段：{var}的响应值：{response_value}，不等于期望值：{expect_value}')
            else:
                if response_value != expect_value:
                    assert_result.append(f'字段：{var}的响应值：{response_value}，不等于期望值：{expect_value}')

        elif expression['operator'] == '>':
            if isinstance(response_value, [str, int]):
                if not response_value > expect_value:
                    assert_result.append(f'字段：{var}的响应值：{response_value}，不大于期望值：{expect_value}')

        elif expression['operator'] == '>=':
            if isinstance(response_value, [str, int]):
                if not response_value >= expect_value:
                    assert_result.append(f'字段：{var}的响应值：{response_value}，不大于等于期望值：{expect_value}')

        elif expression['operator'] == '<':
            if isinstance(response_value, [str, int]):
                if not response_value < expect_value:
                    assert_result.append(f'字段：{var}的响应值：{response_value}，不小于期望值：{expect_value}')

        elif expression['operator'] == '<=':
            if isinstance(response_value, [str, int]):
                if not response_value <= expect_value:
                    assert_result.append(f'字段：{var}的响应值：{response_value}，不小于等于期望值：{expect_value}')

        elif expression['operator'] == '包含':
            if expect_value not in response_value:
                assert_result.append(f'字段：{var}的响应值：{response_value}，不包含期望值：{expect_value}')

        elif expression['operator'] == '不包含':
            if expect_value in response_value:
                assert_result.append(f'字段：{var}的响应值：{response_value}，包含期望值：{expect_value}')

        elif expression['operator'] in ['空', '为空']:
            if response_value:
                assert_result.append(f'字段：{var}的响应值：{response_value}，不为空！')

        elif expression['operator'] in ['不为空', '非空']:
            if not response_value:
                assert_result.append(f'字段：{var}的响应值：{response_value}为空！')
    if assert_result:
        logger.info(f'响应断言不通过，原因为：{assert_result}')
    assert assert_result == []


if __name__ == '__main__':
    # data = read_excel_with_sheetname(
    #     r'E:\newpython\auto_interface\test_data\userinterface\testtable_predata.xlsx')
    # sql = generate_sql_from_excel(data, type='insert')
    # param = [{'dbtype': DBType.MYSQL.value, 'dbname': 'userinterface', 'sql': sql}]
    # print(sql)
    # first = [{'name': 'xiaoming', 'age': 18}, {'name': 'xiaohong', 'age': 15}]
    # second = [{'name': 'xiaohong', 'age': 15}, {'name': 'xiaoming', 'age': 18}]
    # xiaoming = {'name': 'xiaoming', 'age': 18, 'friends': {'name': 'xiaohong', 'age': 15}}
    # print(jsonpath.jsonpath(xiaoming, '$.name')[0])
    # print(jsonpath.jsonpath(xiaoming, '$.friends.name'))
    # print(jsonpath.jsonpath(xiaoming, '$..name'))
    assert_response()
